package fr.greta.centreFormation.dao;


import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import fr.greta.centreFormation.bean.Candidat;
import fr.greta.centreFormation.bean.Cursus;
import fr.greta.centreFormation.bean.Promotion;


public class PromotionHome {

	//private static final java.util.Date Date = null;

	public static int insertPromotion(Promotion unePromotion){

		java.sql.Date debutPromotion = new java.sql.Date(unePromotion.getDebutPromotion().getTime());
		java.sql.Date finPromotion = new java.sql.Date(unePromotion.getFinPromotion().getTime());
		Cursus leCursus = new Cursus();
		leCursus=unePromotion.getCursus();
		int idCursus=leCursus.getIdCursus();

		String insertPromotion = "INSERT INTO promotion (debut_promotion, fin_promotion, id_cursus)VALUES(?,?,?)";
		String insertCandidatPromotion="INSERT INTO candidat_promotion(id_promotion, id_candidat) VALUES(?,?)";


		try{

			Connection connection = Bdd.getConnection();
			PreparedStatement stmtInsertPromo = connection.prepareStatement(insertPromotion, Statement.RETURN_GENERATED_KEYS);

			stmtInsertPromo.setDate(1, debutPromotion);
			stmtInsertPromo.setDate(2, finPromotion);
			stmtInsertPromo.setInt(3, idCursus);

			stmtInsertPromo.executeUpdate();
			ResultSet result= stmtInsertPromo.getGeneratedKeys();
			result.next();

			int idPromotion=result.getInt(1);

			PreparedStatement stmtInsertCandidatPromo = connection.prepareStatement(insertCandidatPromotion);

			for(Candidat unCandidat: unePromotion.getAllCandidats()){

				stmtInsertCandidatPromo.setInt(1, idPromotion);
				stmtInsertCandidatPromo.setInt(2, CandidatHome.selectIdCandidatByIdPersonne(unCandidat.getIdPersonne()));
				stmtInsertCandidatPromo.executeUpdate();
			}

			return idPromotion;

		}catch(Exception e){
			e.getMessage();
			return 0;

		}
	}



	public static boolean updatePromotion(Promotion unePromotion){

		int idPromotion = unePromotion.getIdPromotion();

		java.sql.Date debutPromotion = new java.sql.Date(unePromotion.getDebutPromotion().getTime());
		java.sql.Date finPromotion = new java.sql.Date(unePromotion.getFinPromotion().getTime());

		String updatePromo ="UPDATE promotion SET debut_promotion=?, fin_promotion=? WHERE id_promotion=?";
		String insertCandidatPromo = "INSERT INTO candidat_promotion(id_promotion, id_candidat) VALUES (?,?)";

		try{
			Connection connection = Bdd.getConnection();
			PreparedStatement stmtUpdatePromo = connection.prepareStatement(updatePromo);
			PreparedStatement stmtInsertCandidatPromo= connection.prepareStatement(insertCandidatPromo);

			//update de la table promotion
			stmtUpdatePromo.setDate(1, debutPromotion);
			stmtUpdatePromo.setDate(2, finPromotion);
			stmtUpdatePromo.setInt(3, idPromotion);
			stmtUpdatePromo.executeUpdate();


			//delete toutes les lignes concernant la promotion dans la table candidat_promotion
			deleteAllCandidatPromotionByIdPromotion(idPromotion);

			//insert des nouvelles lignes dans la table candidat_promotion
			for (Candidat unCandidat : unePromotion.getAllCandidats()){
				stmtInsertCandidatPromo.setInt(1, idPromotion);
				stmtInsertCandidatPromo.setInt(2, CandidatHome.selectIdCandidatByIdPersonne(unCandidat.getIdPersonne()));
				stmtInsertCandidatPromo.executeUpdate();
			}

			return true;

		}catch(Exception e){
			e.printStackTrace();
			return false;
		}
	}

	public static boolean updatePromotionSansSesCandidats(Promotion unePromotion){

		int idPromotion = unePromotion.getIdPromotion();
		int idCursus = unePromotion.getCursus().getIdCursus();

		java.sql.Date debutPromotion = new java.sql.Date(unePromotion.getDebutPromotion().getTime());
		java.sql.Date finPromotion = new java.sql.Date(unePromotion.getFinPromotion().getTime());

		String updatePromo ="UPDATE promotion SET debut_promotion=?, fin_promotion=? , id_cursus = ? WHERE id_promotion=?";
		String insertCandidatPromo = "INSERT INTO candidat_promotion(id_promotion, id_candidat) VALUES (?,?)";

		try{
			Connection connection = Bdd.getConnection();
			PreparedStatement stmtUpdatePromo = connection.prepareStatement(updatePromo);
			PreparedStatement stmtInsertCandidatPromo= connection.prepareStatement(insertCandidatPromo);

			//update de la table promotion
			stmtUpdatePromo.setDate(1, debutPromotion);
			stmtUpdatePromo.setDate(2, finPromotion);
			stmtUpdatePromo.setInt(3, idCursus);
			stmtUpdatePromo.setInt(4, idPromotion);
			stmtUpdatePromo.executeUpdate();

			return true;

		}catch(Exception e){
			e.printStackTrace();
			return false;
		}
	}


	public static boolean deletePromotion(Promotion unePromotion){
		int idPromotion = unePromotion.getIdPromotion();
		String sql ="DELETE FROM promotion WHERE id_promotion=?";
		try{
			Connection connection = Bdd.getConnection();
			PreparedStatement canal = connection.prepareStatement(sql);
			canal.setInt(1, idPromotion);
			canal.executeUpdate();
			return true;

		}catch(Exception e) {
			return false;
		}


	}

	public static boolean deleteAllCandidatPromotionByIdPromotion(int idPromotion){

		String deleteCandidats = "DELETE FROM candidat_promotion WHERE id_promotion=?";

		try {
			Connection connection = Bdd.getConnection();
			PreparedStatement stmtdeleteCandidats = connection.prepareStatement(deleteCandidats);
			stmtdeleteCandidats.setInt(1, idPromotion);
			stmtdeleteCandidats.executeUpdate();
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}

	}

	public static ArrayList<Promotion> selectAllPromotion(){

		ArrayList<Promotion> tabPromotion = new ArrayList<Promotion>();
		String sql = "SELECT * FROM promotion";

		try{

			Connection connection = Bdd.getConnection();
			PreparedStatement canal = connection.prepareStatement(sql);
			ResultSet result = canal.executeQuery();

			while(result.next()){

				int id_promotion = result.getInt("id_promotion");
				Date debut_promotion = result.getDate("debut_promotion");
				Date fin_promotion = result.getDate("fin_promotion");

				Promotion promotion = new Promotion();
				promotion.setIdPromotion(id_promotion);
				promotion.setDebutPromotion(debut_promotion);
				promotion.setFinPromotion(fin_promotion);

				Cursus c = CursusHome.selectUnCursusById(result.getInt("id_cursus"));
				promotion.setCursus(c);

				ArrayList<Candidat> lesCandidats = CandidatHome.selectAllCandidatByIdPromotion(id_promotion);
				promotion.setCollectionDeCandidats(lesCandidats);

				tabPromotion.add(promotion);
			}

		}

		catch (Exception e){

			e.printStackTrace();
			return null;
		}
		return tabPromotion;
	}



	public static Promotion selectUnePromotionByid(int idPromotion){
		Promotion promotion = new Promotion();
		String sql = "SELECT * FROM promotion WHERE id_promotion = ?";
		try{
			Connection connection = Bdd.getConnection();
			PreparedStatement canal = connection.prepareStatement(sql);

			canal.setInt(1, idPromotion);
			ResultSet resultat = canal.executeQuery();

			resultat.next();
			promotion.setIdPromotion(resultat.getInt("id_promotion"));

			java.sql.Date dSQL= resultat.getDate("debut_promotion");			
			java.util.Date dJava = new java.util.Date(dSQL.getTime());	
			promotion.setDebutPromotion(dJava);


			dSQL= resultat.getDate("fin_promotion");			
			dJava = new java.util.Date(dSQL.getTime());	
			promotion.setFinPromotion(dJava);

			Cursus c = CursusHome.selectUnCursusById(resultat.getInt("id_cursus"));
			promotion.setCursus(c);

			return promotion;

		}catch(Exception e){
			System.out.println(e.getMessage());
			return null;

		}

	}


	public static ArrayList<Promotion> selectAllPromotionByCandidat(Candidat unCandidat){
		ArrayList<Promotion> lesPromotions= new ArrayList<Promotion>();
		int idCandidat= CandidatHome.selectIdCandidatByIdPersonne(unCandidat.getIdPersonne());

		String selectPromo = "SELECT * FROM promotion p INNER JOIN candidat_promotion cp ON p.id_promotion=cp.id_promotion WHERE cp.id_candidat=?";

		Connection connection;
		try {
			connection = Bdd.getConnection();
			PreparedStatement stmt= connection.prepareStatement(selectPromo);
			stmt.setInt(1, idCandidat);
			ResultSet result = stmt.executeQuery();

			while(result.next()){
				Promotion unePromotion=new Promotion();
				unePromotion.setIdPromotion(result.getInt("id_promotion"));
				unePromotion.setDebutPromotion(result.getDate("debut_promotion"));
				unePromotion.setFinPromotion(result.getDate("fin_promotion"));
				Cursus unCursus = new Cursus();
				unCursus=CursusHome.selectUnCursusById(result.getInt("id_cursus"));
				unePromotion.setCursus(unCursus);
				lesPromotions.add(unePromotion);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return lesPromotions;
	}

	public static ArrayList<Promotion> selectAllPromotionByIdPersonne(int idPersonne){
		ArrayList<Promotion> lesPromotions= new ArrayList<Promotion>();
		int idCandidat= CandidatHome.selectIdCandidatByIdPersonne(idPersonne);

		String selectPromo = "SELECT * FROM promotion p INNER JOIN candidat_promotion cp ON p.id_promotion=cp.id_promotion WHERE cp.id_candidat=?";

		Connection connection;
		try {
			connection = Bdd.getConnection();
			PreparedStatement stmt= connection.prepareStatement(selectPromo);
			stmt.setInt(1, idCandidat);
			ResultSet result = stmt.executeQuery();

			while(result.next()){
				Promotion unePromotion=new Promotion();
				unePromotion.setIdPromotion(result.getInt("id_promotion"));
				unePromotion.setDebutPromotion(result.getDate("debut_promotion"));
				unePromotion.setFinPromotion(result.getDate("fin_promotion"));
				Cursus unCursus = new Cursus();
				unCursus=CursusHome.selectUnCursusById(result.getInt("id_cursus"));
				unePromotion.setCursus(unCursus);
				lesPromotions.add(unePromotion);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return lesPromotions;
	}
	

}

